/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package note.dao;

import note.dbc.DBConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import note.vo.Note;

public class NoteDAO {

    public boolean insert(int id,
            String title,String author,String content) {
        String sql = "INSERT INTO note(id,title,author,content) VALUES(?,?,?,?)";
        //得到连接对象
        Connection conn = DBConnection.getConnection();
        //定义预编译语句对象，此对象可执行动态sql命令。
        PreparedStatement pstmt = null;
        boolean insertSuccessFlag = false;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setString(2, title);
            pstmt.setString(3, author);
            pstmt.setString(4, content);
            //执行pstmt表示的插入命令
            pstmt.executeUpdate();
            insertSuccessFlag = true;
        } catch (SQLException e) {
            System.out.println("插入操作中出现错误！！！");
            System.out.println(e);
        } finally {
            DBConnection.close(conn, pstmt, null);
        }
        return insertSuccessFlag;
    }

    // 修改操作
    public boolean update(int id,
            String title,String author,String content) {
        String sql = "UPDATE note SET title=?,author=?,content=? WHERE id=?";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        boolean updateSuccessFlag = false;
        try {
            pstmt = conn.prepareStatement(sql);
          
            pstmt.setString(1, title);
            pstmt.setString(3, author);
            pstmt.setString(3, content);
            pstmt.setInt(4, id);
            int x = pstmt.executeUpdate();
            if (x > 0) {
                updateSuccessFlag = true;
            }
        } catch (SQLException e) {
            System.out.println("更新操作中出现错误！！！");
            System.out.println(e);
        } finally {
            DBConnection.close(conn, pstmt, null);
        }
        return updateSuccessFlag;
    }
    // 删除操作

    public boolean delete(int id) {
        String sql = "DELETE FROM note WHERE id=?";
        PreparedStatement pstmt = null;
        Connection conn = DBConnection.getConnection();
        boolean deleteSuccessFlag = false;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int i = pstmt.executeUpdate();
            if (i ==1) {
                deleteSuccessFlag = true;
            }
        } catch (SQLException e) {
            System.out.println("删除操作中出现错误！！！");
            System.out.println(e);
        } finally {
            DBConnection.close(conn, pstmt, null);
        }
        return deleteSuccessFlag;
    }
    // 按ID查询

    public Note queryById(int id) {
        Note note = null;
        String sql = "SELECT id,title,author,content FROM note WHERE id=?";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            //此处为什么不用while,用while可不可以
            if (rs.next()) {
                note = new Note();
                note.setId(rs.getInt(1));
                note.setTitle(rs.getString(2));
                note.setAuthor(rs.getString(3));
                note.setContent(rs.getString(4));
            }

        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
        } finally {
            DBConnection.close(conn, pstmt, rs);
        }
        return note;
    }

    public String[] getSingleFiledData() {
        String sql = "SELECT id FROM note";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String[] data = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            data = new String[rowCount];
            rs.first();
            for (int i = 0; i < rowCount; i++) {
                data[i] = rs.getString(1);
                rs.next();
            }
        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {
            DBConnection.close(conn, pstmt, rs);
        }
        return data;
    }

    public void saveTableData(JTable table) {
        String sql = "UPDATE note SET title=?,author=?,content=? WHERE id=?";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        //boolean updateSuccessFlag = false;
        DefaultTableModel mdl = (DefaultTableModel) table.getModel();
        int rowCount = mdl.getRowCount();
        for (int i = 0; i < rowCount; i++) {
            try {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, (String) mdl.getValueAt(i, 1));
                pstmt.setString(2, (String) mdl.getValueAt(i, 2));
                pstmt.setString(3, (String) mdl.getValueAt(i, 3));
                int id = Integer.parseInt((String) mdl.getValueAt(i, 0));
                pstmt.setInt(4, id);
                pstmt.executeUpdate();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            } 
        }
                DBConnection.close(conn, pstmt, null);
            
    }

    public String[][] getAllData() {
        String sql = "SELECT id,title,author,content FROM note order by id";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String[][] data = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            data = new String[rowCount][4];
            rs.first();
            for (int i = 0; i < rowCount; i++) {
                for (int j = 0; j < 4; j++) {
                    data[i][j] = rs.getString(j + 1);
                }
                rs.next();
            }
        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {

            DBConnection.close(conn, pstmt, rs);
        }


        return data;
    }
    public Object[][] getAllData2() {
        String sql = "SELECT id,title,author,content FROM note order by id";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Object[][] data = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            data = new Object[rowCount][4];
            rs.first();
            for (int i = 0; i < rowCount; i++) {
                for (int j = 0; j < 4; j++) {
                    data[i][j] = rs.getObject(j + 1);
                }
                rs.next();
            }
        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {

            DBConnection.close(conn, pstmt, rs);
        }


        return data;
    }

    public String[][] findByLike(String content) {
        String sql = "SELECT id,title,author,content FROM note where" + " title like ?" + " or author like ?" + " or content like ?";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String[][] data = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + content + "%");
            pstmt.setString(2, "%" + content + "%");
            pstmt.setString(3, "%" + content + "%");

            rs = pstmt.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            data = new String[rowCount][4];
            rs.first();
            for (int i = 0; i < rowCount; i++) {
                for (int j = 0; j < 4; j++) {
                    data[i][j] = rs.getString(j + 1);
                }
                rs.next();
            }
        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {
            DBConnection.close(conn, pstmt, rs);
        }

        return data;
    }
     public ArrayList<Note> queryAll() {
       ArrayList<Note> all = new ArrayList<Note>();
        Note note = null;
        String sql = "SELECT  id,title,author,content FROM note ";
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            //此处为什么不用while,用while可不可以
            while (rs.next()) {
                note = new Note();
                note.setId(rs.getInt(1));
                note.setTitle(rs.getString(2));
                note.setAuthor(rs.getString(3));
                note.setContent(rs.getString(4));
                all.add(note);
            }

        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {
            DBConnection.close(conn, pstmt, rs);
        }
        return all;
    }

    public String[][] findByCombo(String title, String author) {
        String sql = "SELECT id,title,author,content FROM note where" + " title like ?" + " and author like ?";

        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String[][] data = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + title + "%");
            pstmt.setString(2, "%" + author + "%");
            rs = pstmt.executeQuery();
            rs.last();
            int rowCount = rs.getRow();
            data = new String[rowCount][4];
            rs.first();
            for (int i = 0; i < rowCount; i++) {
                for (int j = 0; j < 4; j++) {
                    data[i][j] = rs.getString(j + 1);
                }
                rs.next();
            }
        } catch (SQLException e) {
            System.out.println("操作中出现错误！！！");
            System.out.println(e.getMessage());
        } finally {
            DBConnection.close(conn, pstmt, rs);
        }
        return data;
    }
}
